from openpyxl import load_workbook
import pymysql

config = {
    'host': '127.0.0.1',
    'port':3306,
    'user': 'root',
    'password': '670980756',
    'charset': 'utf8mb4',
    #'cursorclass': pymysql.cursors.DictCursor

}
conn = pymysql.connect(**config)
conn.autocommit(1)
cursor = conn.cursor()

name = 'test_api'
print('create database if not exists %s' % name)
# cursor.execute('create database if not exists %s' % name)
conn.select_db(name)
table_name = 'movie_info'
# cursor.execute('create table if not exists %s(id MEDIUMINT NOT NULL AUTO_INCREMENT,name varchar(30),tel varchar(30),primary key (id))' % table_name)
# cursor.execute('create table if not exists %s(id MEDIUMINT NOT NULL AUTO_INCREMENT,'
#                'movie_name varchar(255),'
#                'comment_count varchar(100),'
#                'movie_score varchar(50),'
#                'movie_type varchar(255),'
#                'primary key (id))' % table_name)

wb2 = load_workbook('电影.xlsx')
ws=wb2.get_sheet_names()
for row in wb2:
    for cell in row:
        value=(table_name,cell[0].value,cell[1].value,cell[2].value,cell[3].value)

        print(cell[0].value)
        print(cell[1].value)
        print(cell[2].value)
        print(cell[3].value)

        # sql = 'insert into %s (movie_name,comment_count,movie_score,movie_type) values (%s,%s,%s,%s)', value
        sql = 'insert into %s (movie_name,comment_count,movie_score,movie_type) values (%s,%s,%s,%s)' % (table_name,cell[0].value,cell[1].value,cell[2].value,cell[3].value)
        print(sql)
        cursor.execute('insert into ' + table_name + ' (movie_name,comment_count,movie_score,movie_type) values (%s,%s,%s,%s)', value)

# print(cursor.execute('select * from %s' % table_name))
print("overing...")
